package com.ccp.dev.system.service;

import com.ccp.dev.core.basic.api.ISysDataSourceService;
import com.ccp.dev.core.basic.base.BaseService;
import com.ccp.dev.core.basic.internationalization.LocaleMessageSource;
import com.ccp.dev.core.basic.template.IRollBack;
import com.ccp.dev.core.basic.template.RollbackJdbcTemplate;
import com.ccp.dev.core.basic.util.UUIDUtils;
import com.ccp.dev.core.util.*;
import com.ccp.dev.system.dao.SysDataSourceDao;
import com.ccp.dev.system.model.SysDataSource;
import com.ccp.dev.system.xml.system.SysDataSourceXml;
import com.ccp.dev.system.xml.system.SysDataSourceXmlList;
import com.ccp.dev.system.util.XmlUtil;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.text.StringEscapeUtils;
import org.dom4j.Document;
import org.dom4j.Element;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * SYS_DATA_SOURCE service类
 *
 * @author zhaowj
 */
@Service
public class SysDataSourceService extends BaseService<SysDataSource> implements ISysDataSourceService {

    @Resource
    SysDataSourceDao sysDataSourceDao;

    @Resource
    protected LocaleMessageSource localeMessageSource;

    /**
     * 通过别名查询数据源信息
     *
     * @param alias 数据源别名
     * @param id    数据源id
     * @return 数据源对象
     */
    public SysDataSource getByAliasAndId(String alias, String id) {
        return sysDataSourceDao.getByAliasAndId(alias, id);
    }

    /**
     * 添加或更新数据源
     *
     * @param sysDataSource 数据源对象
     */
    @Transactional(rollbackFor = Exception.class)
    public int addOrUpdate(SysDataSource sysDataSource) {
        int count;
        sysDataSource.setSettingJson(StringEscapeUtils.unescapeHtml4(sysDataSource.getSettingJson()));
        if (sysDataSource.getId() == null) {
            sysDataSource.setId(UUIDUtils.getUUIDFor32());
            count = sysDataSourceDao.add(sysDataSource);
        } else {
            count = sysDataSourceDao.update(sysDataSource);
        }
        return count;
    }

    /**
     * 删除SYS_DATA_SOURCE
     *
     * @param id 数据源id
     * @return 删除的条数
     */
    @Transactional(rollbackFor = Exception.class)
    public int delSysDataSourceById(String id) {
        return sysDataSourceDao.delById(id);
    }

    /**
     * 导出全部系统数据源
     *
     * @param sysDataSources 数据源集合
     * @throws Exception 异常
     */
    @Transactional(rollbackFor = Exception.class)
    public String exportXml(List<SysDataSource> sysDataSources) throws Exception {
        SysDataSourceXmlList sysDataSourceXmlList = new SysDataSourceXmlList();
        List<SysDataSourceXml> list = new ArrayList<>();
        for (SysDataSource sysDataSource : sysDataSources) {
            SysDataSourceXml sysDataSourceXml = this.exportsysDataSource(sysDataSource);
            list.add(sysDataSourceXml);
        }
        sysDataSourceXmlList.setSysDataSourceXmlList(list);
        return XmlBeanUtil.marshall(sysDataSourceXmlList,
                SysDataSourceXmlList.class);
    }

    /**
     * 导出部分系统数据源
     *
     * @param tableIds id数组
     * @throws Exception 异常
     */
    @Transactional(rollbackFor = Exception.class)
    public String exportXml(String[] tableIds) throws Exception {
        SysDataSourceXmlList sysDataSourceXmlList = new SysDataSourceXmlList();
        List<SysDataSourceXml> list = new ArrayList<>();
        for (int i = 0; i < tableIds.length; i++) {
            SysDataSource sysDataSource = sysDataSourceDao.getById(tableIds[i]);
            SysDataSourceXml sysDataSourceXml = this.exportsysDataSource(sysDataSource);
            list.add(sysDataSourceXml);
        }
        sysDataSourceXmlList.setSysDataSourceXmlList(list);
        return XmlBeanUtil.marshall(sysDataSourceXmlList,
                SysDataSourceXmlList.class);
    }

    /**
     * 导出表的信息
     *
     * @param sysDataSource 数据源类
     * @return SysDataSourceXml
     */
    private SysDataSourceXml exportsysDataSource(SysDataSource sysDataSource) {
        SysDataSourceXml sysDataSourceXml = new SysDataSourceXml();
        String id = sysDataSource.getId();
        if (BeanUtils.isNotIncZeroEmpty(id)) {
            //导出系统数据源
            sysDataSourceXml.setDataSource(sysDataSource);
        }
        return sysDataSourceXml;
    }

    /**
     * 导入系统数据源Xml
     *
     * @param inputStream 输入流
     * @throws Exception 异常
     */
    @Transactional(rollbackFor = Exception.class)
    public void importXml(InputStream inputStream) throws Exception {
        Document doc = Dom4jUtil.loadXml(inputStream);
        if (doc == null) {
            throw new Exception(localeMessageSource.getMessage("dataSource.importXml.formatError"));
        }
        Element root = doc.getRootElement();
        // 验证格式是否正确
        XmlUtil.checkXmlFormat(root, "system", "dataSources");
        String xmlStr = root.asXML();
        SysDataSourceXmlList sysDataSourceXmlList = (SysDataSourceXmlList) XmlBeanUtil
                .unmarshall(xmlStr, SysDataSourceXmlList.class);
        List<SysDataSourceXml> list = sysDataSourceXmlList
                .getSysDataSourceXmlList();
        for (SysDataSourceXml sysDataSourceXml : list) {
            // 导入表 并解析相关信息
            this.importSysDataSourceXml(sysDataSourceXml);
        }
    }

    /**
     * 导入时生成系统数据源
     *
     * @param sysDataSourceXml 数据源xml导入导出封装的类
     * @throws Exception 异常
     */
    public void importSysDataSourceXml(SysDataSourceXml sysDataSourceXml) throws Exception {
        String id = UUIDUtils.getUUIDFor32();
        SysDataSource sysDataSource = sysDataSourceXml.getDataSource();
        if (BeanUtils.isEmpty(sysDataSource)) {
            throw new Exception(localeMessageSource.getMessage("dataSource.importXml.dataIsEmpty"));
        }
        String name = sysDataSource.getName();
        if ("".equals(name)) {
            throw new Exception(localeMessageSource.getMessage("dataSource.importXml.nameIsEmpty"));
        }
        String alias = sysDataSource.getAlias();
        if ("".equals(alias)) {
            throw new Exception(localeMessageSource.getMessage("dataSource.importXml.aliasIsEmpty"));
        }

        List<SysDataSource> sysDataSourceList = sysDataSourceDao.queryListALL();
        for (SysDataSource dataSource : sysDataSourceList) {
            String aliasName = dataSource.getAlias();
            if (aliasName.equals(alias)) {
                throw new Exception(localeMessageSource.getMessage("dataSource.importXml.aliasIsRepeat"));
            }
        }
        sysDataSource.setId(id);
        sysDataSourceDao.add(sysDataSource);
    }

    /**
     * 查询所有数据源信息
     *
     * @return 数据源对象集合
     */
    public List<SysDataSource> queryListALL() {
        return sysDataSourceDao.queryListALL();
    }


    /**
     * 利用Java反射机制把dataSource成javax.sql.DataSource对象
     *
     * @param sysDataSource 数据源对象
     * @return javax.sql.DataSource
     */
    public DataSource getDsFromSysSource(SysDataSource sysDataSource) {

        try {
            // 获取对象
            Class<?> dataSourceClass;
            if (StringUtil.isNotEmpty(sysDataSource.getClassPath())) {
                dataSourceClass = Class.forName(sysDataSource.getClassPath());
                DataSource sqldataSource;
                // 初始化对象
                sqldataSource = (DataSource) dataSourceClass.newInstance();

                // 开始set它的属性
                String settingJson = sysDataSource.getSettingJson();
                JSONArray ja = JSONArray.fromObject(StringEscapeUtils.unescapeHtml4(settingJson));
                for (int i = 0; i < ja.size(); i++) {
                    JSONObject jo = ja.getJSONObject(i);
                    Object value;
                    if (StringUtil.isNotEmpty(jo.getString("value"))) {
                        value = BeanUtils.convertByActType(jo.getString("type"), jo.getString("value"));
                    } else {
                        value = "";
                    }
                    BeanUtils.setProperty(sqldataSource, jo.getString("name"), value);
                }

                // 如果有初始化方法，需要调用，必须是没参数的
                String initMethodStr = sysDataSource.getInitMethod();
                if (!StringUtil.isEmpty(initMethodStr)) {
                    Method method = dataSourceClass.getMethod(initMethodStr);
                    method.invoke(sqldataSource);
                }
                return sqldataSource;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 验证连接
     *
     * @param sysDataSource 数据源
     * @return 是否连接成功
     */
    public boolean checkConnection(SysDataSource sysDataSource) {
        DataSource dataSource = getDsFromSysSource(sysDataSource);
        String closeMethod = sysDataSource.getCloseMethod();
        boolean b = false;
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            b = true;
        } catch (Exception e) {
            // 调用关闭
            if (!StringUtil.isEmpty(closeMethod)) {
                String cp = closeMethod.split("\\|")[0];
                String mn = closeMethod.split("\\|")[1];
                try {
                    Class<?> cpClass = Class.forName(cp);
                    Method method = cpClass.getMethod(mn, null);
                    method.invoke(null, null);
                } catch (Exception e1) {
                    e1.printStackTrace();
                }
            }
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return b;
    }


    public boolean validSql(String sql,SysDataSource sysDataSource){
        DataSource dataSource = getDsFromSysSource(sysDataSource);
        String closeMethod = sysDataSource.getCloseMethod();
        boolean b = false;
        Connection connection = null;
        try {
//            connection = dataSource.getConnection();
//            Statement statement = connection.createStatement();
//            ResultSet resultSet = statement.executeQuery(sql);
            final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            RollbackJdbcTemplate rollbackJdbcTemplate = (RollbackJdbcTemplate) AppUtil.getBean("rollbackJdbcTemplate");
            Map<String, Object> param = new HashMap();
            b = (Boolean)rollbackJdbcTemplate.executeRollBack(new IRollBack() {
                @Override
                public Object execute(String script, Map<String, Object> map) {
                    try {
                        jdbcTemplate.execute(script);
                    } catch (Exception var4) {
                        var4.printStackTrace();
                        return false;
                    }

                    return true;
                }
            }, sql, param);
        } catch (Exception e) {
            // 调用关闭
            if (!StringUtil.isEmpty(closeMethod)) {
                String cp = closeMethod.split("\\|")[0];
                String mn = closeMethod.split("\\|")[1];
                try {
                    Class<?> cpClass = Class.forName(cp);
                    Method method = cpClass.getMethod(mn, null);
                    method.invoke(null, null);
                } catch (Exception e1) {
                    e1.printStackTrace();
                }
            }
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return b;
    }

    /**
     * 通过别名查询数据源信息
     *
     * @param alias 别名
     * @return 数据源对象
     */
    @Override
    public SysDataSource getByAlias(String alias) {
        return sysDataSourceDao.getByAlias(alias);
    }

    public static void main(String[] args) {
        Connection con = null;
        try {
//            Class.forName("com.gbase.jdbc.Driver");
            System.out.println("数据库驱动加载成功！");
            con = DriverManager.getConnection("jdbc:gbase://localhost:5258/testdb", "DEVPLATFORM", "DEVPLATFORM");
            System.out.println("数据库连接成功！");
        } catch (Exception e1) {
            e1.printStackTrace();
            System.out.println("数据库驱动加载失败！");
        }
    }
}

